package com.fone.player.storage;

import java.io.File;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Build.VERSION;

import com.fone.core.adaptor.JNIUtil;
import com.fone.player.ApplicationManage;
import com.fone.player.client.Callback;
import com.fone.player.client.Error;
import com.fone.player.client.LoginspaceRst;
import com.fone.player.client.Request;
import com.fone.player.entity.OfflineCache;
import com.fone.player.entity.OfflineCacheFragment;
import com.fone.player.entity.PlayRecord;
import com.fone.player.sns.UserInfoManager;
import com.fone.player.util.AESUtil;
import com.fone.player.util.Base64;
import com.fone.player.util.FoneUtil;
import com.fone.player.util.L;
import com.fone.player.util.SystemUtil;

/**
 * 
 * 数据库升级适配器
 * 
 * @author wangyang
 * @since 2014年7月3日
 */
public class UpgradeDataBaseAdapter implements IFoneDatabase {
	private static final String TAG = UpgradeDataBaseAdapter.class
			.getSimpleName();
	private static final UpgradeDataBaseAdapter mInstance = new UpgradeDataBaseAdapter();

	private UpgradeDataBaseAdapter() {

	}

	public static UpgradeDataBaseAdapter getInstance() {
		return mInstance;
	}

	/**
	 * 4.0升级4.0.5
	 */
	public void upgradeDatabase4_0_TO_4_0_5(SQLiteDatabase sqliteDatabase) {
		// OfflineCacheFragment 重新创建
		String offlineCacheFragment = "BEGIN TRANSACTION;";
		L.v(TAG, "upgradeDatabase4_0_TO_4_0_5", "offlineCacheFragment="
				+ offlineCacheFragment);
		sqliteDatabase.execSQL(offlineCacheFragment);

		offlineCacheFragment = CREATE_OFFLINE_CACHE_FRAGMENT_TEMPORARY_TABLE_4_0_TO_4_0_5;
		L.v(TAG, "upgradeDatabase4_0_TO_4_0_5", "offlineCacheFragment="
				+ offlineCacheFragment);
		sqliteDatabase.execSQL(offlineCacheFragment);

		offlineCacheFragment = "INSERT INTO "
				+ TB_OFFLINE_CACHE_FRAGMENT_BACK_UP + " SELECT * FROM "
				+ TB_OFFLINE_CACHE_FRAGMENT + ";";
		L.v(TAG, "upgradeDatabase4_0_TO_4_0_5", "offlineCacheFragment="
				+ offlineCacheFragment);
		sqliteDatabase.execSQL(offlineCacheFragment);

		offlineCacheFragment = "DROP TABLE " + TB_OFFLINE_CACHE_FRAGMENT + ";";
		L.v(TAG, "upgradeDatabase4_0_TO_4_0_5", "offlineCacheFragment="
				+ offlineCacheFragment);
		sqliteDatabase.execSQL(offlineCacheFragment);

		offlineCacheFragment = CREATE_OFFLINE_CACHE_FRAGMENT_TABLE;
		L.v(TAG, "upgradeDatabase4_0_TO_4_0_5", "offlineCacheFragment="
				+ offlineCacheFragment);
		sqliteDatabase.execSQL(offlineCacheFragment);

		offlineCacheFragment = "INSERT INTO " + TB_OFFLINE_CACHE_FRAGMENT
				+ " SELECT " + OFFLINE_CACHE_FRAGMENT_CID + ","
				+ OFFLINE_CACHE_FRAGMENT_INDEX + ","
				+ OFFLINE_CACHE_FRAGMENT_ALREADY_SIZE + ","
				+ OFFLINE_CACHE_FRAGMENT_TOTAL_SIZE + ","
				+ OFFLINE_CACHE_FRAGMENT_DOWNLOAD_STATE + ","
				+ OFFLINE_CACHE_FRAGMENT_DEFINITION_TYPE + ","
				+ OFFLINE_CACHE_FRAGMENT_DURATION + ","
				+ OFFLINE_CACHE_FRAGMENT_URL + ","
				+ OFFLINE_CACHE_FRAGMENT_URL_MD5 + " FROM "
				+ TB_OFFLINE_CACHE_FRAGMENT_BACK_UP + ";";
		L.v(TAG, "upgradeDatabase4_0_TO_4_0_5", "offlineCacheFragment="
				+ offlineCacheFragment);
		sqliteDatabase.execSQL(offlineCacheFragment);

		offlineCacheFragment = "DROP TABLE "
				+ TB_OFFLINE_CACHE_FRAGMENT_BACK_UP + ";";
		L.v(TAG, "upgradeDatabase4_0_TO_4_0_5", "offlineCacheFragment="
				+ offlineCacheFragment);
		sqliteDatabase.execSQL(offlineCacheFragment);

		offlineCacheFragment = "COMMIT;";
		L.v(TAG, "upgradeDatabase4_0_TO_4_0_5", "offlineCacheFragment="
				+ offlineCacheFragment);
		sqliteDatabase.execSQL(offlineCacheFragment);

		// TB_FILE_DOWNLOAD 重新创建
		String deleteTable = "DROP TABLE IF EXISTS '" + TB_FILE_DOWNLOAD + "';";
		L.v(TAG, "upgradeDatabase4_0_TO_4_0_5", "deleteTable=" + deleteTable);
		sqliteDatabase.execSQL(deleteTable);
		L.v(TAG, "upgradeDatabase4_0_TO_4_0_5", "create table filedownload="
				+ CREATE_FILE_DOWNLOAD_TABLE);
		sqliteDatabase.execSQL(CREATE_FILE_DOWNLOAD_TABLE);
	}

	public void upgradeDatabase(SQLiteDatabase sqliteDatabase) {

		// 添加旧版本未完成的任务
		addOfflineCacheOldNonFinishList(sqliteDatabase);
		
		// 升级缓存
		upgradeOfflineCache(sqliteDatabase);

		// 升级播放记录
		upgradePlayRecord(sqliteDatabase);

		// 删除无用表
		deleteOldTable(sqliteDatabase);

	}

	/**
	 * 
	 * 升级离线缓存表
	 * 
	 * @param sqliteDatabase
	 *            数据库操作对象
	 * @return void
	 */
	public void upgradeOfflineCache(SQLiteDatabase sqliteDatabase) {

		L.i(TAG, "upgradeOfflineCache", "start");

		List<OfflineCache> offlineCacheList = new ArrayList<OfflineCache>();
		List<OfflineCacheFragment> offlineCacheFragmentList = new ArrayList<OfflineCacheFragment>();
		// 兼容已缓存的文件表
		try {

			StringBuilder cachedSql = new StringBuilder();
			cachedSql
					.append("select cid,name,thumbnail_url,completed_size,total_size,status,");
			cachedSql
					.append("download_type,total_time as duration,unit_count,folder_path,detail_url_ccid ");
			cachedSql.append("from download_tasked");
			Cursor cachedCursor = sqliteDatabase.rawQuery(cachedSql.toString(),
					null);
			if (cachedCursor != null) {
				String[] names = cachedCursor.getColumnNames();
				L.v(TAG, "upgradeOfflineCache",
						"cachedCursor name=" + Arrays.toString(names));
				while (cachedCursor.moveToNext()) {
					OfflineCache offlineCache = getOfflineCacheFinishByCursor(cachedCursor);
					offlineCacheList.add(offlineCache);
				}
				cachedCursor.close();
			}
			L.i(TAG, "upgradeOfflineCache", "cached file upgrade success");
		} catch (Exception e) {
			if (e != null) {
				e.fillInStackTrace();
				L.e(TAG, "upgradeOfflineCache",
						"cached error e=" + e.getMessage());
			}

		}

		// 兼容分片表
		try {

			StringBuilder fragSql = new StringBuilder();
			fragSql.append("select t.cid,u.file_path,u.status,u.total_size,u.completed_size,u.unit_index,u.unit_time from download_united as u,download_tasked as t where u.task_id=t._id");
			Cursor fragCursor = sqliteDatabase.rawQuery(fragSql.toString(),
					null);
			if (fragCursor != null) {
				String[] names = fragCursor.getColumnNames();
				L.v(TAG, "upgradeOfflineCache",
						"fragCursor name=" + Arrays.toString(names));
				while (fragCursor.moveToNext()) {

					OfflineCacheFragment offlineCacheFragment = getOfflineCacheFragmentByCursor(fragCursor);
					offlineCacheFragmentList.add(offlineCacheFragment);
				}
				fragCursor.close();
			}
			L.i(TAG, "upgradeOfflineCache", "cached fragment upgrade success");
		} catch (Exception e) {
			if (e != null) {
				e.fillInStackTrace();
				L.e(TAG, "upgradeOfflineCache",
						"frag error e=" + e.getMessage());
			}
		}
		for (OfflineCache offlineCache : offlineCacheList) {
			L.i(TAG,
					"upgradeOfflineCache",
					"ccid=" + offlineCache.getCacheCCID() + " name="
							+ offlineCache.getCacheName()
							+ offlineCache.getCacheEpisodeNum());
		}
		try {
			// 合并数据库
			sqliteDatabase.beginTransaction();

			// 文件夹(先测试只兼容文件层级,后期再测试文件夹层级)
			for (OfflineCache offlineCache : offlineCacheList) {
				ContentValues contentValues = new ContentValues();
				contentValues.put(OFFLINE_CACHE_FOLDER_CID,
						offlineCache.getCacheCID());
				contentValues.put(OFFLINE_CACHE_FOLDER_CCID,
						offlineCache.getCacheCCID());
				contentValues.put(OFFLINE_CACHE_FOLDER_VIDEO_ID,
						offlineCache.getCacheVideoId());
				// 把名字里面的数字去掉
				// String folderName = offlineCache.getCacheName().replaceAll(
				// "\\d+$", "");
				contentValues.put(OFFLINE_CACHE_FOLDER_NAME,
						offlineCache.getCacheName());
				contentValues.put(OFFLINE_CACHE_FOLDER_IMAGE_URL,
						offlineCache.getCacheImageUrl());
				contentValues.put(OFFLINE_CACHE_FOLDER_EPISODE_NUM,
						offlineCache.getCacheEpisodeNum());
				contentValues.put(OFFLINE_CACHE_FOLDER_EPISODE_UPDATE_INFO,
						offlineCache.getCacheEpisodeUpdateInfo());
				contentValues.put(OFFLINE_CACHE_FOLDER_CONTENT_TYPE,
						offlineCache.getCacheContentType());
				contentValues.put(OFFLINE_CACHE_FOLDER_XYZPLAY_URL,
						offlineCache.getCacheXYZPlayUrl());
				contentValues.put(OFFLINE_CACHE_FOLDER_DRAMA_URL,
						offlineCache.getCacheDramaUrl());
				contentValues.put(OFFLINE_CACHE_FOLDER_CREATE_TIME,
						offlineCache.getCacheCreateTime());

				// 重复的文件夹做忽略处理
				if (VERSION.SDK_INT < 8) {
					try {
						sqliteDatabase.insertOrThrow(TB_OFFLINE_CACHE_FOLDER,
								null, contentValues);
					} catch (Exception e) {
					}

				} else {

					sqliteDatabase.insertWithOnConflict(
							TB_OFFLINE_CACHE_FOLDER, null, contentValues,
							SQLiteDatabase.CONFLICT_IGNORE);
				}

			}

			// 文件
			for (OfflineCache offlineCache : offlineCacheList) {
				ContentValues contentValues = new ContentValues();
				contentValues.put(OFFLINE_CACHE_FILE_CID,
						offlineCache.getCacheCID());
				contentValues.put(OFFLINE_CACHE_FILE_CCID,
						offlineCache.getCacheCCID());
				contentValues.put(OFFLINE_CACHE_FILE_VIDEO_ID,
						offlineCache.getCacheVideoId());
				contentValues.put(OFFLINE_CACHE_FILE_NAME,
						offlineCache.getCacheName());
				contentValues.put(OFFLINE_CACHE_FILE_IMAGE_URL,
						offlineCache.getCacheImageUrl());
				contentValues.put(OFFLINE_CACHE_FILE_EPISODE_NUM,
						offlineCache.getCacheEpisodeNum());
				contentValues.put(OFFLINE_CACHE_FILE_ALREADY_SIZE,
						offlineCache.getCacheAlreadySize());
				contentValues.put(OFFLINE_CACHE_FILE_TOTAL_SIZE,
						offlineCache.getCacheTotalSize());
				contentValues.put(OFFLINE_CACHE_FILE_DOWNLOAD_STATE,
						offlineCache.getCacheDownloadState());
				contentValues.put(OFFLINE_CACHE_FILE_CONTENT_TYPE,
						offlineCache.getCacheContentType());
				contentValues.put(OFFLINE_CACHE_FILE_DEFINITION_TYPE,
						offlineCache.getCacheDefinitionType());
				contentValues.put(OFFLINE_CACHE_FILE_DOWNLOAD_TYPE,
						offlineCache.getCacheDownloadType());
				contentValues.put(OFFLINE_CACHE_FILE_DURATION,
						offlineCache.getCacheDuration());
				contentValues.put(OFFLINE_CACHE_FILE_FRAGMENT_COUNT,
						offlineCache.getCacheFragmentCount());
				contentValues.put(OFFLINE_CACHE_FILE_STORAGE_PATH,
						offlineCache.getCacheStoragePath());
				contentValues.put(OFFLINE_CACHE_FILE_XYZPLAY_URL,
						offlineCache.getCacheXYZPlayUrl());
				contentValues.put(OFFLINE_CACHE_FILE_SORT_INDEX,
						offlineCache.getSortIndex());
				// 重复的文件夹做忽略处理
				if (VERSION.SDK_INT < 8) {
					try {
						sqliteDatabase.insertOrThrow(TB_OFFLINE_CACHE_FILE,
								null, contentValues);
					} catch (Exception e) {
					}

				} else {

					sqliteDatabase
							.insertWithOnConflict(TB_OFFLINE_CACHE_FILE, null,
									contentValues,
									SQLiteDatabase.CONFLICT_IGNORE);
				}

			}

			for (OfflineCacheFragment offlineCacheFragment : offlineCacheFragmentList) {
				ContentValues contentValues = new ContentValues();
				contentValues.put(OFFLINE_CACHE_FRAGMENT_CID,
						offlineCacheFragment.getCacheCID());
				contentValues.put(OFFLINE_CACHE_FRAGMENT_INDEX,
						offlineCacheFragment.getCacheCurrentIndex());
				contentValues.put(OFFLINE_CACHE_FRAGMENT_DOWNLOAD_STATE,
						offlineCacheFragment.getCacheDownloadState());
				contentValues.put(OFFLINE_CACHE_FRAGMENT_URL,
						offlineCacheFragment.getCacheFragmentUrl());
				contentValues.put(OFFLINE_CACHE_FRAGMENT_URL_MD5,
						offlineCacheFragment.getCacheFragmentUrlMD5());
				contentValues.put(OFFLINE_CACHE_FRAGMENT_ALREADY_SIZE,
						offlineCacheFragment.getCacheAlreadySize());
				contentValues.put(OFFLINE_CACHE_FRAGMENT_TOTAL_SIZE,
						offlineCacheFragment.getCacheTotalSize());
				contentValues.put(OFFLINE_CACHE_FRAGMENT_DURATION,
						offlineCacheFragment.getCacheDuration());
				contentValues.put(OFFLINE_CACHE_FRAGMENT_DEFINITION_TYPE,
						offlineCacheFragment.getCacheDefinitionType());
				// 重复的文件碎片做忽略处理
				if (VERSION.SDK_INT < 8) {
					try {
						sqliteDatabase.insertOrThrow(TB_OFFLINE_CACHE_FRAGMENT,
								null, contentValues);
					} catch (Exception e) {
					}

				} else {

					sqliteDatabase.insertWithOnConflict(
							TB_OFFLINE_CACHE_FRAGMENT, null, contentValues,
							SQLiteDatabase.CONFLICT_IGNORE);
				}

			}
			sqliteDatabase.setTransactionSuccessful();
			sqliteDatabase.endTransaction();
		} catch (Exception e) {
			if (e != null) {
				e.fillInStackTrace();
				L.e(TAG, "upgradeOfflineCache",
						"save database error e=" + e.getMessage());
			}
		}
	}

	/**
	 * 升级播放记录表
	 * 
	 * @param sqliteDatabase
	 *            数据库操作对象
	 */
	public void upgradePlayRecord(SQLiteDatabase sqliteDatabase) {
		L.i(TAG, "upgradePlayRecord", "start");
		List<PlayRecord> playRecordList = new ArrayList<PlayRecord>();
		try {
			StringBuilder updateSql = new StringBuilder();
			updateSql.append("select * from played");
			Cursor cursor = sqliteDatabase.rawQuery(updateSql.toString(), null);
			if (cursor != null) {
				while (cursor.moveToNext()) {
					PlayRecord playRecord = getPlayRecordByCursor(cursor);
					playRecordList.add(playRecord);
				}
				cursor.close();
			}
		} catch (Exception e) {
			if (e != null) {
				e.fillInStackTrace();
				L.e(TAG, "upgradePlayRecord",
						"select error e=" + e.getMessage());
			}
		}
		try {
			for (PlayRecord playRecord : playRecordList) {
				ContentValues contentValues = new ContentValues();
				contentValues.put(PLAY_RECORD_CID,
						playRecord.getPlayRecordCID());
				contentValues.put(PLAY_RECORD_CCID,
						playRecord.getPlayRecordCCID());
				contentValues.put(PLAY_RECORD_CLID,
						playRecord.getPlayRecordCLID());
				contentValues.put(PLAY_RECORD_VIDEO_ID,
						playRecord.getPlayRecordVideoID());
				contentValues.put(PLAY_RECORD_TYPE,
						playRecord.getPlayRecordType());
				contentValues.put(PLAY_RECORD_CONTENT_TYPE,
						playRecord.getPlayRecordContentType());
				contentValues.put(PLAY_RECORD_NAME,
						playRecord.getPlayRecordName());
				contentValues.put(PLAY_RECORD_IMAGE_URL,
						playRecord.getPlayRecordImageUrl());
				contentValues.put(PLAY_RECORD_PLAY_URL,
						playRecord.getPlayRecordPlayUrl());
				contentValues.put(PLAY_RECORD_ALREADY_PLAY_TIME,
						playRecord.getPlayRecordAlreadyPlayTime());
				contentValues.put(PLAY_RECORD_TOTAL_TIME,
						playRecord.getPlayRecordTotalTime());
				contentValues.put(PLAY_RECORD_CTEATE_TIME,
						playRecord.getPlayRecordCreateTime());
				contentValues.put(PLAY_RECORD_IS_VIP,
						playRecord.getPlayRecordIsVIP() ? 1 : 0);
				contentValues.put(PLAY_RECORD_DESCRIPTION,
						playRecord.getPlayRecordDescription());
				contentValues.put(PLAY_RECORD_REMOVE_URL,
						playRecord.getPlayRecordRemoveUrl());
				contentValues.put(PLAY_RECORD_FAVOURITE_URL,
						playRecord.getPlayRecordFavouriteUrl());
				contentValues.put(PLAY_RECORD_DETAIL_URL,
						playRecord.getPlayRecordDetailUrl());
				contentValues.put(PLAY_RECORD_SHARE_URL,
						playRecord.getPlayRecordShareUrl());
				contentValues.put(PLAY_RECORD_SYNCHRONZIE_TYPE,
						playRecord.getPlayRecordSynchronzieType());
				contentValues.put(PLAY_RECORD_EPISODE_UPDATE_INFO,
						playRecord.getPlayRecordEpisodeUpdateInfo());
				contentValues.put(PLAY_RECORD_EXTERNAL_URL,
						playRecord.getPlayRecordExternalUrl());
				contentValues.put(PLAY_RECORD_IS_SHOW_PLAY_BUTTON,
						playRecord.getPlayRecordIsShowPlayButton() ? 1 : 0);
				sqliteDatabase.insert(TB_PLAY_RECORD, null, contentValues);
			}
		} catch (Exception e) {
			if (e != null) {
				e.fillInStackTrace();
				L.e(TAG, "upgradePlayRecord",
						"save database error e=" + e.getMessage());
			}
		}

	}

	/**
	 * 升级用户
	 */
	public void upgradeUser() {

		boolean isOldUser = SharedPreferenceModule.getInstance().getBoolean(
				"is_old_user", true);
		if (isOldUser) {

			// 获取旧的seqid(做兼容)
			String seqid = getOldSeqId();

			if (seqid != null && !"".equals(seqid)) {

				SharedPreferenceModule.getInstance().setDefaultString("seqid",
						seqid);

				// 旧用户自动登录
				oldUserAutoLogin(seqid);

			}
			L.v(TAG, "upgradeUser", "seqid=" + seqid);
			SharedPreferenceModule.getInstance().setBoolean("is_old_user",
					false);
		}

	}

	/**
	 * 自动登录
	 */
	public void oldUserAutoLogin(String seqid) {
		L.v(TAG, "oldUserAutoLogin", "start");
		if (FoneUtil.isNetOk(ApplicationManage.getGlobalContext())) {
			L.v(TAG, "autoLogin", "start logining");

			// 初始化网络请求
			Request.initializeInstance(ApplicationManage.getGlobalContext());

			Request.getInstance().updateCipher(seqid);

			Request.getInstance()
					.loginspace(1, 0, 0, null, new LoginCallBack());
		}
	}

	private static class LoginCallBack implements Callback<LoginspaceRst> {

		@Override
		public void onSuccess(final LoginspaceRst t) {
			L.v(TAG, "autoLogin LoginCallBack onSuccess", t.toString());
			if (t.result != 0) {
				if (t.error != null) {
					L.v(TAG, "autoLogin onSuccess", "errorcode : "
							+ t.error.errorcode);
					try {
						switch (Integer.valueOf(t.error.errorcode)) {
						case 110:// 用户名不存在
						case 111:// 用户名或密码错误
						case 117:// 请检查用户名
							// FoneUtil.showToast(ApplicationManage.getGlobalContext(),
							// "帐号密码不匹配");
							SharedPreferenceModule.getInstance()
									.setDefaultString("seqid", "");
							Request.getInstance().updateCipher("");
							break;
						default:
							// FoneUtil.showToast(ApplicationManage.getGlobalContext(),
							// "登录失败");
							SharedPreferenceModule.getInstance()
									.setDefaultString("seqid", "");
							Request.getInstance().updateCipher("");
							break;
						}
					} catch (Exception e) {
						L.v(TAG, "autoLogin LoginCallBack onSuccess",
								e.getMessage() + "");
						SharedPreferenceModule.getInstance().setDefaultString(
								"seqid", "");
						Request.getInstance().updateCipher("");
					}
					return;
				}
			}
			// 本地化用户信息
			new Thread() {
				public void run() {
					UserInfoManager.saveLoginUserInfo(t,
							UserInfoManager.UPDATE_USERINFO);
					L.v(TAG, "autoLogin LoginCallBack onSuccess",
							" autoLogin success");
				};
			}.start();

		}

		@Override
		public void onFailure(Error error) {
			L.v(TAG, "autoLogin LoginCallBack onFailure", "isNetworkError : "
					+ error.isNetworkError() + " status : " + error.getStatus()
					+ " reason : " + error.getReason());
			SharedPreferenceModule.getInstance().setDefaultString("seqid", "");
			Request.getInstance().updateCipher("");
		}
	}

	/**
	 * 获取seqid
	 * 
	 */
	public String getOldSeqId() {
		String seqid = "";
		String oldKey = "23E5BBF9&9#02E5B";
		String cipher = SharedPreferenceModule.getInstance().getDefaultString(
				"cipher");
		L.i(TAG, "getOldSeqId", "cipher=" + cipher);
		// 测试
		// cipher =
		// "cDHrp7v2CXGuDfi2QlFn2nXrdij4WL69SIBmy_48HGrbvJ_Uf2q8zcvVWbgd5fQZEa3ITZ2EWzPSwQuTB-n3agJ1yOLoNkffNJEaEiO_YbWfzcG7jPBiKIQyQm_tngbSLfm0lx73sffhScklZ4zuMIZ1equULLqpk5CZu1EnriK0q9m3zmBDjLIeF2fPM4y858SQFGbFWYwG8w1OqaXqMLLqpYnxlB2dYF5ViyIxv_NrTrnwAZC5aLJfC8RP0obl";
		String urlDecodeCipher;
		try {
			urlDecodeCipher = URLDecoder.decode(cipher, "UTF-8");

			byte[] base64decodeCipher = Base64.decode(urlDecodeCipher,
					Base64.URL_SAFE | Base64.NO_WRAP);
			String result = AESUtil.decrypt(oldKey, base64decodeCipher);
			// 测试
			// result =
			// "seqid=4545456&cv=10.26.7.3245.8.8001&imei=860311029069465&";
			seqid = result.substring(result.indexOf("seqid=") + 6,
					result.indexOf("&"));

			L.i(TAG, "getOldSeqId", "seqid=" + seqid + " cipherString="
					+ result);

		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return seqid;
	}

	/**
	 * 
	 * 删除数据库旧表
	 * 
	 * @param sqliteDatabase
	 *            数据库操作对象
	 * @return void
	 */
	public void deleteOldTable(SQLiteDatabase sqliteDatabase) {
		// 删除未下载完成的分片
		try {
			StringBuilder fragSql = new StringBuilder();
			fragSql.append("select * from download_unit");
			Cursor fragCursor = sqliteDatabase.rawQuery(fragSql.toString(),
					null);
			if (fragCursor != null) {
				while (fragCursor.moveToNext()) {
					String filePath = fragCursor.getString(fragCursor
							.getColumnIndex("file_path"));
					filePath = filePath != null ? filePath.substring(0,
							filePath.lastIndexOf("/") - 1) : "";
					File file = new File(filePath);
					if (file.exists()) {
						file.delete();
						L.v(TAG, "deleteOldTable", "delete success filePath="
								+ filePath);
					} else {
						L.w(TAG, "deleteOldTable", "no exist filePath="
								+ filePath);
					}
				}
				fragCursor.close();
			}
		} catch (Exception e) {
			if (e != null) {
				e.fillInStackTrace();
				L.e(TAG, "deleteOldTable", "frag error e=" + e.getMessage());
			}

		}
		try {
			// 删除无用的表
			String deleteTable = "DROP TABLE IF EXISTS 'bitmap';";
			sqliteDatabase.execSQL(deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
			deleteTable = "DROP TABLE IF EXISTS 'bitmap';";
			sqliteDatabase.execSQL(deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
			deleteTable = "DROP TABLE IF EXISTS 'download_report';";
			sqliteDatabase.execSQL(deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
			deleteTable = "DROP TABLE IF EXISTS 'download_soft';";
			sqliteDatabase.execSQL(deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
			deleteTable = "DROP TABLE IF EXISTS 'download_tasked';";
			sqliteDatabase.execSQL(deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
			deleteTable = "DROP TABLE IF EXISTS 'download_unit';";
			sqliteDatabase.execSQL(deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
			deleteTable = "DROP TABLE IF EXISTS 'download_united';";
			sqliteDatabase.execSQL(deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
			deleteTable = "DROP TABLE IF EXISTS 'emoji';";
			sqliteDatabase.execSQL(deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
			deleteTable = "DROP TABLE IF EXISTS 'faverite';";
			sqliteDatabase.execSQL(deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
			deleteTable = "DROP TABLE IF EXISTS 'gift';";
			sqliteDatabase.execSQL(deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
			deleteTable = "DROP TABLE IF EXISTS 'local';";
			sqliteDatabase.execSQL(deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
			deleteTable = "DROP TABLE IF EXISTS 'local_folder';";
			sqliteDatabase.execSQL(deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
			deleteTable = "DROP TABLE IF EXISTS 'notification';";
			sqliteDatabase.execSQL(deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
			deleteTable = "DROP TABLE IF EXISTS 'notification_temp';";
			sqliteDatabase.execSQL(deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
			deleteTable = "DROP TABLE IF EXISTS 'played';";
			sqliteDatabase.execSQL(deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
			L.v(TAG, "deleteOldTable", "delete table success deletetable="
					+ deleteTable);
		} catch (Exception e) {
			L.v(TAG, "deleteOldTable",
					"delete table error msg=" + e != null ? e.getMessage() : "");
		}

	}

	/**
	 * 
	 * 升级离线缓存表
	 * 
	 * @return void
	 */
	public void addOfflineCacheOldNonFinishList(SQLiteDatabase sqliteDatabase) {

		L.i(TAG, "addOfflineCacheOldNonFinishList", "start");
		String isTableSql = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='download_task'";
		Cursor isTableCursor = sqliteDatabase.rawQuery(isTableSql, null);
		if (isTableCursor == null || isTableCursor.getCount() < 1) {
			if (isTableCursor != null) {
				isTableCursor.close();
			}
			return;
		}

		List<OfflineCache> offlineCacheList = new ArrayList<OfflineCache>();

		// 兼容正在缓存的文件表
		try {
			StringBuilder updateSql = new StringBuilder();
			updateSql
					.append("select cid,name,thumbnail_url,completed_size,total_size,status,");
			updateSql
					.append("download_type,unit_time as duration,unit_count,folder_path,play_url as xyzplay ");
			updateSql.append("from download_task");
			Cursor cachingCursor = sqliteDatabase.rawQuery(
					updateSql.toString(), null);
			if (cachingCursor != null) {
				String[] names = cachingCursor.getColumnNames();
				L.v(TAG, "addOfflineCacheOldNonFinishList",
						"cachingCursor name=" + Arrays.toString(names));
				while (cachingCursor.moveToNext()) {
					OfflineCache offlineCache = getOfflineCacheNonFinishByCursor(cachingCursor);
					offlineCacheList.add(offlineCache);
				}
				cachingCursor.close();
			}
			L.i(TAG, "addOfflineCacheOldNonFinishList",
					"caching file upgrade success");
		} catch (Exception e) {
			if (e != null) {
				e.fillInStackTrace();
				L.e(TAG, "addOfflineCacheOldNonFinishList", "caching error e="
						+ e.getMessage());
			}

		}
		for (OfflineCache offlineCache : offlineCacheList) {
			L.i(TAG,
					"addOfflineCacheOldNonFinishList",
					"ccid=" + offlineCache.getCacheCCID() + " name="
							+ offlineCache.getCacheName()
							+ offlineCache.getCacheEpisodeNum());
		}

		// 将旧的未缓存完成的视频,从新开始缓存,避免出错
		if (offlineCacheList.size() > 0) {
			StorageModule.getInstance().addCacheList(offlineCacheList);
		}

		String deleteTable = "DROP TABLE IF EXISTS 'download_task';";
		sqliteDatabase.execSQL(deleteTable);
		L.v(TAG, "addOfflineCacheOldNonFinishList",
				"delete table success deletetable=" + deleteTable);
	}

	private OfflineCache getOfflineCacheNonFinishByCursor(Cursor cursor) {
		OfflineCache offlineCache = new OfflineCache();
		String CIDStr = cursor.getString(cursor.getColumnIndex("cid"));
		long CID = CIDStr == null ? 0 : Long.parseLong(CIDStr);
		offlineCache.setCacheCID(CID);

		String xyzplay = cursor.getString(cursor.getColumnIndex("xyzplay"));
		L.v(TAG, "getOfflineCacheByCursor", "xyzplay=" + xyzplay);
		String xyzplayUrl = (xyzplay == null ? "" : xyzplay.replace(
				"http://v.100tv.com:80/vi", "[host]"));
		L.v(TAG, "getOfflineCacheByCursor", "xyzplayUrl=" + xyzplayUrl);
		offlineCache.setCacheXYZPlayUrl(xyzplayUrl);

		String CCIDStr = FoneUtil.getCCIdByUrl(xyzplay);
		long CCID = CCIDStr == null ? 0 : Long.parseLong(CCIDStr);
		// ccid=ccid+cid,破坏分组
		offlineCache.setCacheCCID(CCID + CID);
		L.v(TAG, "getOfflineCacheByCursor", "CCIDStr=" + CCIDStr + " CCID="
				+ CCID);
		String CLIDStr = FoneUtil.getCLIdByUrl(xyzplay);
		long CLID = CLIDStr == null ? 0 : Long.parseLong(CLIDStr);
		offlineCache.setCacheCLID(CLID);
		L.v(TAG, "getOfflineCacheByCursor", "CLIDStr=" + CLIDStr + " CLID="
				+ CLID);
		String videoIdStr = FoneUtil.getVideoIdByUrl(xyzplay);
		long videoId = videoIdStr == null ? 0 : Long.parseLong(videoIdStr);
		offlineCache.setCacheVideoId(videoId);
		L.v(TAG, "getOfflineCacheByCursor", "videoIdStr=" + videoIdStr
				+ " videoId=" + videoId);
		offlineCache.setCacheName(cursor.getString(cursor
				.getColumnIndex("name")));
		offlineCache.setCacheImageUrl(cursor.getString(cursor
				.getColumnIndex("thumbnail_url")));
		offlineCache.setCacheDownloadState(OfflineCache.CACHE_STATE_QWAITING);
		offlineCache.setCacheAlreadySize(0);
		offlineCache.setCacheTotalSize(0);
		offlineCache.setCacheFragmentCount(0);
		offlineCache.setCacheDownloadType(cursor.getInt(cursor
				.getColumnIndex("download_type")));
		offlineCache.setCacheContentType(OfflineCache.CACHE_CONTENT_TYPE_OTHER);
		long duration = cursor.getInt(cursor.getColumnIndex("duration"));
		offlineCache.setCacheDuration(duration);
		offlineCache.setCacheStoragePath(JNIUtil
				.getStorePath(SystemUtil.CACHE_DOWNLOAD_TYPE));
		offlineCache.setSortIndex(0);
		offlineCache.setCacheFromPage(OfflineCache.CACHE_FROM_PAGE_UPGRADE);
		return offlineCache;
	}

	private OfflineCache getOfflineCacheFinishByCursor(Cursor cursor) {
		OfflineCache offlineCache = new OfflineCache();
		String folderPath = cursor.getString(cursor
				.getColumnIndex("folder_path"));
		offlineCache.setCacheStoragePath(folderPath);
		offlineCache.setCacheXYZPlayUrl(folderPath);
		String CIDStr = cursor.getString(cursor.getColumnIndex("cid"));
		long CID = CIDStr == null ? 0 : Long.parseLong(CIDStr);
		offlineCache.setCacheCID(CID);

		String CCIDStr = cursor.getString(cursor
				.getColumnIndex("detail_url_ccid"));
		long CCID = CCIDStr == null ? 0 : Long.parseLong(CCIDStr);
		// ccid=ccid+cid,破坏分组
		offlineCache.setCacheCCID(CCID + CID);
		L.v(TAG, "getOfflineCacheByCursor", "CCIDStr=" + CCIDStr + " CCID="
				+ CCID);
		offlineCache.setCacheName(cursor.getString(cursor
				.getColumnIndex("name")));
		offlineCache.setCacheImageUrl(cursor.getString(cursor
				.getColumnIndex("thumbnail_url")));
		offlineCache.setCacheDownloadState(OfflineCache.CACHE_STATE_FINISH);
		long totalSize = cursor.getInt(cursor.getColumnIndex("total_size"));
		offlineCache.setCacheAlreadySize(totalSize);
		offlineCache.setCacheTotalSize(totalSize);
		offlineCache.setCacheFragmentCount(cursor.getInt(cursor
				.getColumnIndex("unit_count")));
		offlineCache.setCacheDownloadType(cursor.getInt(cursor
				.getColumnIndex("download_type")));
		offlineCache.setCacheContentType(OfflineCache.CACHE_CONTENT_TYPE_OTHER);
		long duration = cursor.getInt(cursor.getColumnIndex("duration"));
		offlineCache.setCacheDuration(duration);
		offlineCache.setSortIndex(0);
		offlineCache.setCacheFromPage(OfflineCache.CACHE_FROM_PAGE_UPGRADE);
		return offlineCache;
	}

	private OfflineCacheFragment getOfflineCacheFragmentByCursor(Cursor cursor) {
		OfflineCacheFragment offlineCacheFragment = new OfflineCacheFragment();
		String CIDStr = cursor.getString(cursor.getColumnIndex("cid"));
		long CID = CIDStr == null ? 0 : Long.parseLong(CIDStr);
		offlineCacheFragment.setCacheCID(CID);
		String filePath = cursor.getString(cursor.getColumnIndex("file_path"));
		L.v(TAG, "getOfflineCacheFragmentByCursor", "filePath=" + filePath);
		offlineCacheFragment.setCacheFragmentStoragePath(filePath);
		offlineCacheFragment.setCacheFragmentUrl(filePath);
		offlineCacheFragment
				.setCacheDownloadState(OfflineCache.CACHE_STATE_FINISH);
		long tatalSize = cursor.getInt(cursor.getColumnIndex("total_size"));
		offlineCacheFragment.setCacheAlreadySize(tatalSize);
		offlineCacheFragment.setCacheTotalSize(tatalSize);
		offlineCacheFragment.setCacheCurrentIndex(cursor.getInt(cursor
				.getColumnIndex("unit_index")));
		L.v(TAG, "getOfflineCacheFragmentByCursor", "unit_index="
				+ offlineCacheFragment.getCacheCurrentIndex());
		long unitTime = cursor.getInt(cursor.getColumnIndex("unit_time"));
		offlineCacheFragment.setCacheDuration(unitTime);
		return offlineCacheFragment;
	}

	private PlayRecord getPlayRecordByCursor(Cursor cursor) {
		PlayRecord playRecord = new PlayRecord();
		String CIDStr = cursor.getString(cursor.getColumnIndex("cid"));
		long CID = CIDStr == null ? 0 : Long.parseLong(CIDStr);
		playRecord.setPlayRecordCID(CID);

		String xyzplay = cursor.getString(cursor.getColumnIndex("url"));
		String xyzplayUrl = (xyzplay == null ? "" : xyzplay.replace(
				"http://v.100tv.com:80/vi", "[host]"));
		playRecord.setPlayRecordPlayUrl(xyzplayUrl);

		String CCIDStr = FoneUtil.getCCIdByUrl(xyzplay);
		long CCID = CCIDStr == null ? 0 : Long.parseLong(CCIDStr);
		playRecord.setPlayRecordCCID(CCID);

		String videoIdStr = FoneUtil.getVideoIdByUrl(xyzplay);
		long videoId = videoIdStr == null ? 0 : Long.parseLong(videoIdStr);
		playRecord.setPlayRecordVideoID(videoId);

		int localMediaType = cursor.getInt(cursor
				.getColumnIndex("localMedia_type"));
		switch (localMediaType) {
		case 0:
			playRecord.setPlayRecordType(PlayRecord.RECORD_PLAYER_NETWORK);
			String contentType = cursor
					.getString(cursor.getColumnIndex("type"));
			if (contentType != null) {
				if ("电视剧".equals(contentType)) {
					playRecord
							.setPlayRecordContentType(OfflineCache.CACHE_CONTENT_TYPE_TV);
				} else if ("电影".equals(contentType)) {
					playRecord
							.setPlayRecordContentType(OfflineCache.CACHE_CONTENT_TYPE_MOVIE);
				} else if ("动画".equals(contentType)) {
					playRecord
							.setPlayRecordContentType(OfflineCache.CACHE_CONTENT_TYPE_ANIMATION);
				} else if ("综艺".equals(contentType)) {
					playRecord
							.setPlayRecordContentType(OfflineCache.CACHE_CONTENT_TYPE_VARIETY);
				} else if ("原创".equals(contentType)) {
					playRecord
							.setPlayRecordContentType(OfflineCache.CACHE_CONTENT_TYPE_ORIGINAL);
				} else if ("视频直播".equals(contentType)) {
					playRecord
							.setPlayRecordContentType(OfflineCache.CACHE_CONTENT_TYPE_LIVE_VIDEO);
				} else if ("音频直播".equals(contentType)) {
					playRecord
							.setPlayRecordContentType(OfflineCache.CACHE_CONTENT_TYPE_LIVE_AUDIO);
				} else if ("新闻".equals(contentType)) {
					playRecord
							.setPlayRecordContentType(OfflineCache.CACHE_CONTENT_TYPE_NEWS);
				} else if ("短片".equals(contentType)) {
					playRecord
							.setPlayRecordContentType(OfflineCache.CACHE_CONTENT_TYPE_SHORT_FILM);
				} else if ("网页".equals(contentType)) {
					playRecord
							.setPlayRecordContentType(OfflineCache.CACHE_CONTENT_TYPE_WEB_PAGE);
				} else {
					// 其他转成全部
					playRecord
							.setPlayRecordContentType(OfflineCache.CACHE_CONTENT_TYPE_ALL);
				}
			}
			break;
		case 3:
			playRecord.setPlayRecordType(PlayRecord.RECORD_PLAYER_LOCAL);
			break;
		default:
			break;
		}
		playRecord.setPlayRecordName(cursor.getString(cursor
				.getColumnIndex("name")));
		playRecord.setPlayRecordImageUrl(cursor.getString(cursor
				.getColumnIndex("image_url")));
		playRecord.setPlayRecordAlreadyPlayTime(cursor.getLong(cursor
				.getColumnIndex("played_time")));
		playRecord.setPlayRecordTotalTime(cursor.getLong(cursor
				.getColumnIndex("media_time")));
		playRecord.setPlayRecordCreateTime(cursor.getLong(cursor
				.getColumnIndex("create_time")));
		playRecord.setPlayRecordIsVIP(cursor.getInt(cursor
				.getColumnIndex("isVip")) == 1);

		String remove = cursor.getString(cursor.getColumnIndex("delete_url"));
		// String removeUrl = (remove == null ? "" : remove.replace(
		// "http://v.100tv.com/vi", "[host]"));
		playRecord.setPlayRecordRemoveUrl(remove);

		playRecord.setPlayRecordDetailUrl("");
		playRecord.setPlayRecordFavouriteUrl("");
		playRecord.setPlayRecordExternalUrl("");

		String share = cursor.getString(cursor.getColumnIndex("share_url"));
		// String shareUrl = (share == null ? "" : share.replace(
		// "http://v.100tv.com/vi", "[host]"));
		playRecord.setPlayRecordShareUrl(share);

		playRecord
				.setPlayRecordSynchronzieType(PlayRecord.PLAY_RECORD_SYNCHRONZIE_ADD_WAIT);
		return playRecord;
	}

}
